package net.lunjin.dao.impl;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import net.lunjin.bean.People;
import net.lunjin.dao.PeopleDao;
import finaler.util.Page;
import finaler.util.PageList;
import finaler.util.SQLUtil;

public class PeopleDaoImpl extends MiddleAbstractDao<People> implements PeopleDao {

	@Override
	protected Class<People> getEntityClass() {
		return People.class;
	}

	@Override
	public int getCount(int typeflag) throws Exception {
		String sql  = "select count(id) from people where typeflag = " +typeflag;
		return super.getIntBySql(sql);
	}

	@Override
	public int getCountWantPeople(BigDecimal orgId) throws Exception {
		String sql  = "select count(id) from people where typeflag = 0 and orgId= " +orgId;
		return super.getIntBySql(sql);
	}

	@Override
	public List<People> getTop5WantPeople(BigDecimal orgId) throws Exception {
		String sql  = "select * from people where typeflag=0 and orgId="+orgId +" order by id desc limit 0,5";
		return super.findBySqlToBeanList(sql);
	}

	@Override
	public List<People> getAllProxy(BigDecimal orgId) throws Exception {
		String sql  = "select * from people where typeflag=1 and orgId="+orgId;
		return super.findBySqlToBeanList(sql);
	}
	
	@Override
	public List<Map<String, Object>> getTop5Proxy() throws Exception {
		String sql  = "select a.*,b.orgname  from people a left join organizations b on a.orgid= b.id where typeflag=1 order by id desc limit 0,5";
		return super.findBySqlToMapList(sql);
	}

	@Override
	public People findPeople(BigDecimal orgId, String telipqq, String typeflag) throws Exception {
		String sql  = "select * from people where orgId = "+orgId+ " and telipqq='"+telipqq+"'" +" and typeflag = " +typeflag ;
		return super.findBySqlToBean(sql);
	}

	@Override
	public PageList getWantPeople(Page page) throws Exception {
		StringBuffer whereSql = new StringBuffer();
		whereSql.append(" where typeflag = 0" );
		if(page.getParameter("companyId")!=null && page.getParameter("companyId").equals("")==false ){
			whereSql.append(" and orgid = "+page.getParameter("companyId"));
		}
		PageList pageList = new PageList(page);//固定
		StringBuffer sqlCount = new StringBuffer();
		sqlCount.append("select count(1) from people ");
		sqlCount.append( whereSql );
		int dataCount = super.getIntBySql( sqlCount.toString() );//取总
		pageList.setTotal(dataCount);//总数
		
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append("  select a.id,a.telipqq,a.orgid,a.createtime,b.orgname,b.orgshortname");
		queryListSql.append("  from people a   ");
		queryListSql.append("  left join (select id,orgname,orgshortname from organizations ) b  on b.id = a.orgid  ");
		queryListSql.append( whereSql );
		queryListSql.append(" order by id desc" );
		SQLUtil pageSQL = new SQLUtil(queryListSql);//固定
		pageSQL.setFirstResult( page.getStart() );//固定
		pageSQL.setMaxResults( page.getRange() );//固定
		List<Map<String, Object>> dataList = super.findBySqlToMapList(pageSQL.getSql());//数据集
		pageList.setList(dataList);
		return pageList;
	}

	@Override
	public void deleteByOrgid(BigDecimal orgId) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append(" delete from people  where orgid="+orgId);
		super.executeSql(sql.toString());
	}

	@Override
	public void deleteLowComWant() throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append(" delete from people where orgid in ");
		sql.append(" ( ");
		sql.append(" select gg.id from ( ");
		sql.append(" select * from  ( ");
		sql.append(" select c.id,round((c.avgycoin+c.avgdcoin+c.avgrcoin+c.avgkcoin)/4) as avgzcoin ");
		sql.append(" from ( ");
		sql.append(" select a.id,b.* ");
		sql.append(" from  organizations a ");
		sql.append(" left join ");
		sql.append(" (select orgid,count(orgid) as rmcount, ");
		sql.append(" round(avg(ycoin)) as avgycoin, ");
		sql.append(" round(avg(dcoin)) as avgdcoin, ");
		sql.append(" round(avg(rcoin)) as avgrcoin, ");
		sql.append(" round(avg(kcoin)) as avgkcoin  ");
		sql.append(" from remark group by orgid) b  ");
		sql.append(" on  a.id=b.orgid   ");
		sql.append(" ) c   ");
		sql.append(" ) z where z.avgzcoin <60 ");
		sql.append(" ) gg ");
		sql.append(" ) ");
		sql.append(" and autoFlag = 1 and typeflag = 0 ");
		super.executeSql(sql.toString());
	}

}
